# -*- coding:utf-8 -*-
from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.table2Json_v2 import Table2JsonByYM
from com.zzh.utils.requestFieldMapDBField import requestFieldMapToDBField_YM
from com.zzh.utils.sqlUtils.SqlUtils import SqlUtils
import sys
default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    reload(sys)
    sys.setdefaultencoding(default_encoding)

import time
localTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
"""
表名：zzh_member

说明：会员信息表（包含个人和单位）
"""

class MemberDaoYM(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections_v2()


    def queryMemberById(self, id):
        """
        根据ID查询信息    查询    zzh_user
        :param :
        :return:
        单体查询，按照id查询
        """
        sql = "SELECT * FROM zzh_member WHERE member_id=%s"
        cur = self.conn.cursor()
        total_count = cur.execute(sql,[id])
        if total_count:
            result = cur.fetchall()
            cur.close()
            self.conn.close()
            userOauthJson = Table2JsonByYM.member_list_to_json(result)
            return total_count,userOauthJson
        else:
            cur.close()
            self.conn.close()
            return 0, None


    def queryMemberByUserId(self, userId):
        """
        根据ID查询信息    查询    zzh_user
        :param :
        :return:
        单体查询，按照id查询
        """
        sql = "SELECT * FROM zzh_member WHERE user_id=%s"
        cur = self.conn.cursor()
        total_count = cur.execute(sql,[userId])
        if total_count:
            result = cur.fetchall()

            cur.close()
            self.conn.close()
            userOauthJson = Table2JsonByYM.member_list_to_json(result)
            return total_count,userOauthJson
        else:
            cur.close()
            self.conn.close()
            return 0, None

    '''
    def queryMemberPointByUserId(self, userId):
        """
        根据ID查询信息    查询    zzh_user
        :param :
        :return:
        单体查询，按照id查询
        """
        sql = "SELECT * FROM zzh_member WHERE user_id=%s"
        cur = self.conn.cursor()
        total_count = cur.execute(sql, [userId])
        if total_count:
            result = cur.fetchall()

            cur.close()
            self.conn.close()
            userOauthJson = Table2JsonByYM.member_list_to_json(result)
            return userOauthJson[6]
        else:
            cur.close()
            self.conn.close()
            return 0, None
  '''
    def queryMemberByCondition(self, **kwargs):
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        page_index = kwargs['pageIndex']
        PAGE_SIZE = kwargs['pageSize']
        fieldMap = requestFieldMapToDBField_YM.memberMap#要改的这个
        whereSentence = SqlUtils.getOption(conditions, fieldMap)
        cur = self.conn.cursor()
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "SELECT * FROM zzh_member WHERE " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "SELECT * FROM zzh_member WHERE " + whereSentence
        rs = cur.execute(sql)

        total_count = cur.execute(sql)
        if total_count:
            result = cur.fetchall()
            cur.close()
            self.conn.close()
            if (page_index * PAGE_SIZE) < total_count:
                start = (page_index - 1) * PAGE_SIZE
                end = page_index * PAGE_SIZE
                userOauthPart = result[start: end]
                userOauthJSON = Table2JsonByYM.member_list_to_json(userOauthPart)
                return total_count, userOauthJSON
            else:
                start = (page_index - 1) * PAGE_SIZE
                userOauthPart = result[start: ]
                userOauthJSON = Table2JsonByYM.member_list_to_json(userOauthPart)
                return total_count, userOauthJSON

        else:
            cur.close()
            self.conn.close()
            return 0,None



    def selectMember(self, id):
        """
        根据ID查询信息，返回元组

        """
        sql = "SELECT * FROM zzh_member WHERE member_id=%s "

        cur = self.conn.cursor()
        rs = cur.execute(sql, [id])
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr

    def selectMemberByUserId(self, id):
        """
        根据ID查询信息，返回元组

        """
        sql = "SELECT * FROM zzh_member WHERE user_id=%s "

        cur = self.conn.cursor()
        rs = cur.execute(sql, [id])
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr

    def selectMemberByUserIdNotJson(self, id):
        """
        根据ID查询信息，返回元组

        """
        sql = "SELECT * FROM zzh_member WHERE user_id=%s "

        cur = self.conn.cursor()
        rs = cur.execute(sql, [id])
        cur.close()
        self.conn.close()
        return rs



    def insertMember(self,Info):
        """


        """


        userId = Info['userId']
        invited_code = Info['invited_code']  #
        member_code = Info['member_code']
        member_name = Info['member_name']



        member_email = Info['member_email']  #
        member_points = Info['member_points']
        member_balance = Info['member_balance']  #
        member_expired = Info['member_expired']
        industry_id = Info['industry_id']


        province_id = Info['province_id']  #
        city_id = Info['city_id']
        district_id = Info['district_id']  #
        member_favorite_count = Info['member_favorite_count']
        member_address = Info['member_address']


        member_busness = Info['member_busness']  #
        member_type = Info['member_type']
        sp_knowledge = Info['sp_knowledge']  #
        sp_revenue = Info['sp_revenue']
        sp_employee = Info['sp_employee']


        sp_industrycode = Info['sp_industrycode']  #
        tm_industrycode = Info['tm_industrycode']
        tm_deptcode = Info['tm_deptcode']  #
        tm_level = Info['tm_level']
        register_code = Info['tm_level']
        is_vip = 0


        cur = self.conn.cursor()
        sql = 'INSERT INTO zzh_member (user_id,invited_code,member_code,member_name,   ' \
              '    member_email,member_points,member_balance,member_expired,industry_id,' \
              '      province_id,city_id,district_id,member_favorite_count,member_address,' \
              '    member_busness,member_type,sp_knowledge,sp_revenue,sp_employee,   ' \
              '     sp_industrycode,tm_industrycode,tm_deptcode,tm_level,register_code,is_vip) VALUES ' \
              '     (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s )'
        rs=cur.execute(sql,[userId,invited_code,member_code,member_name,member_email,member_points,member_balance,member_expired,industry_id, province_id,city_id,district_id,member_favorite_count,member_address,member_busness,member_type,sp_knowledge,sp_revenue,sp_employee,sp_industrycode,tm_industrycode,tm_deptcode,tm_level,register_code ,is_vip])
        self.conn.commit()
        cur.close()
        self.conn.close()
        if rs:
            return rs
        else:
            return None


    def updateMember(self,Info):#

        id = Info['id']  # 必填，请传递user_id
        userId = Info['userId']
        invited_code = Info['invited_code']  #
        member_code = Info['member_code']
        member_name = Info['member_name']



        member_email = Info['member_email']  #
        member_points = Info['member_points']
        member_balance = Info['member_balance']  #
        member_expired = Info['member_expired']
        industry_id = Info['industry_id']


        province_id = Info['province_id']  #
        city_id = Info['city_id']
        district_id = Info['district_id']  #
        member_favorite_count = Info['member_favorite_count']
        member_address = Info['member_address']


        member_busness = Info['member_busness']  #
        member_type = Info['member_type']
        sp_knowledge = Info['sp_knowledge']  #
        sp_revenue = Info['sp_revenue']
        sp_employee = Info['sp_employee']


        sp_industrycode = Info['sp_industrycode']  #
        tm_industrycode = Info['tm_industrycode']
        tm_deptcode = Info['tm_deptcode']  #
        tm_level = Info['tm_level']
        register_code = Info['tm_level']
        is_vip = Info['is_vip']


        cur = self.conn.cursor()
        sql = 'UPDATE zzh_member SET user_id=%s,invited_code=%s,member_code=%s,member_name=%s,' \
             '  member_email=%s,member_points=%s,member_balance=%s,member_expired=%s ,industry_id=%s,' \
             'province_id=%s,city_id=%s,district_id=%s,member_favorite_count=%s ,member_address=%s,' \
             'member_busness=%s,member_type=%s,sp_knowledge=%s,sp_revenue=%s ,sp_employee=%s, '\
             ' sp_industrycode=%s,tm_industrycode=%s,tm_deptcode=%s,tm_level=%s,register_code=%s ,is_vip=%s '\
            'WHERE member_id=%s'
        rs=cur.execute(sql,[userId,invited_code,member_code,member_name,

                            member_email,member_points,member_balance,member_expired,industry_id,

                            province_id,city_id,district_id,member_favorite_count,

                            member_address,member_busness,member_type,sp_knowledge,sp_revenue,

                            sp_employee,sp_industrycode,tm_industrycode,tm_deptcode,tm_level ,register_code,is_vip,id ])

        self.conn.commit()
        sql2= "SELECT * FROM zzh_member WHERE member_id=%s"
        rs2 = cur.execute(sql2,[id])
        if rs2:
            result = cur.fetchall()
            cur.close()
            self.conn.close()
            userOauthJSON = Table2JsonByYM.member_list_to_json(result)
            return rs2, userOauthJSON
        else:
            cur.close()
            self.conn.close()
            return 0, None

    def updateMemberByUserId2(self, Info):  #

        id = Info['id']  # 必填，请传递user_id
        userId = Info['userId']
        invited_code = Info['invited_code']  #
        member_code = Info['member_code']
        member_name = Info['member_name']

        member_email = Info['member_email']  #
        member_points = Info['member_points']
        member_balance = Info['member_balance']  #
        member_expired = Info['member_expired']
        industry_id = Info['industry_id']

        province_id = Info['province_id']  #
        city_id = Info['city_id']
        district_id = Info['district_id']  #
        member_favorite_count = Info['member_favorite_count']
        member_address = Info['member_address']

        member_busness = Info['member_busness']  #
        member_type = Info['member_type']
        sp_knowledge = Info['sp_knowledge']  #
        sp_revenue = Info['sp_revenue']
        sp_employee = Info['sp_employee']

        sp_industrycode = Info['sp_industrycode']  #
        tm_industrycode = Info['tm_industrycode']
        tm_deptcode = Info['tm_deptcode']  #
        tm_level = Info['tm_level']
        register_code = Info['tm_level']
        is_vip = Info['is_vip']

        cur = self.conn.cursor()
        sql = 'UPDATE zzh_member SET user_id=%s,invited_code=%s,member_code=%s,member_name=%s,' \
              '  member_email=%s,member_points=%s,member_balance=%s,member_expired=%s ,industry_id=%s,' \
              'province_id=%s,city_id=%s,district_id=%s,member_favorite_count=%s ,member_address=%s,' \
              'member_busness=%s,member_type=%s,sp_knowledge=%s,sp_revenue=%s ,sp_employee=%s, ' \
              ' sp_industrycode=%s,tm_industrycode=%s,tm_deptcode=%s,tm_level=%s,register_code=%s,is_vip = %s  ' \
              'WHERE user_id=%s'
        rs = cur.execute(sql, [userId, invited_code, member_code, member_name,

                               member_email, member_points, member_balance, member_expired, industry_id,

                               province_id, city_id, district_id, member_favorite_count,

                               member_address, member_busness, member_type, sp_knowledge, sp_revenue,

                               sp_employee, sp_industrycode, tm_industrycode, tm_deptcode, tm_level, register_code,
                               is_vip, userId])

        self.conn.commit()
        cur.close()

        curUserStatus = self.conn.cursor()
        sqlcurUserStatus = "UPDATE zzh_user SET user_status=2 WHERE user_id=%s"
        rs = curUserStatus.execute(sqlcurUserStatus, [userId])
        self.conn.commit()
        curUserStatus.close()

        self.conn.close()

        if rs:
            return rs
        else:
            return None




    def updateMemberByUserId(self,Info):#

        member_id = Info['member_id']  #
        userId = Info['user_id']
        invited_code = Info['invited_code']  #
        member_code = Info['member_code']
        member_name = Info['member_name']



        member_email = Info['member_email']  #
        member_points = Info['member_points']
        member_balance = Info['member_balance']  #
        member_expired = Info['member_expired']
        industry_id = Info['industry_id']


        province_id = Info['province_id']  #
        city_id = Info['city_id']
        district_id = Info['district_id']  #
        member_favorite_count = Info['member_favorite_count']
        member_address = Info['member_address']


        member_busness = Info['member_busness']  #
        member_type = Info['member_type']
        sp_knowledge = Info['sp_knowledge']  #
        sp_revenue = Info['sp_revenue']
        sp_employee = Info['sp_employee']


        sp_industrycode = Info['sp_industrycode']  #
        tm_industrycode = Info['tm_industrycode']
        tm_deptcode = Info['tm_deptcode']  #
        tm_level = Info['tm_level']
        register_code = Info['tm_level']
        is_vip = Info['is_vip']

        cur = self.conn.cursor()
        sql = 'UPDATE zzh_member SET user_id=%s,invited_code=%s,member_code=%s,member_name=%s,' \
             '  member_email=%s,member_points=%s,member_balance=%s,member_expired=%s ,industry_id=%s,' \
             'province_id=%s,city_id=%s,district_id=%s,member_favorite_count=%s ,member_address=%s,' \
             'member_busness=%s,member_type=%s,sp_knowledge=%s,sp_revenue=%s ,sp_employee=%s, '\
             ' sp_industrycode=%s,tm_industrycode=%s,tm_deptcode=%s,tm_level=%s,register_code=%s,is_vip = %s  '\
            'WHERE user_id=%s'
        rs=cur.execute(sql,[userId,invited_code,member_code,member_name,

                            member_email,member_points,member_balance,member_expired,industry_id,

                            province_id,city_id,district_id,member_favorite_count,

                            member_address,member_busness,member_type,sp_knowledge,sp_revenue,

                            sp_employee,sp_industrycode,tm_industrycode,tm_deptcode,tm_level ,register_code,is_vip,userId ])

        self.conn.commit()
        cur.close()


        curUserStatus = self.conn.cursor()
        sqlcurUserStatus = "UPDATE zzh_user SET user_status=2 WHERE user_id=%s"
        rsUserStatus =curUserStatus.execute(sqlcurUserStatus, [userId])
        self.conn.commit()
        curUserStatus.close()

        self.conn.close()

        if rs:
            return rs
        else:
            return None




    def deleteMember(self, ids):
        """
        根据ID删除数据表信息，ID需要以数组形式传递
        :param adminIdArray: id 构成的数组 []
        :return:
        """
        sql = "delete from zzh_member WHERE member_id=%s"
        cur = self.conn.cursor()
        for id in ids:
            rs = cur.execute(sql, [id])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs
